package com.apes.fn.fico.reimbursement.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;

import java.util.Map;
import java.util.Objects;

/**
 * Created by lishikun on 2021/2/1 11:14
 */
@Component
public class ExpenseDebitDao extends ApesDao {

    public JSONArray findAllByBorrower(String borrower){
        JSONObject request = new JSONObject();
        String sql ="" +
                " SELECT \n" +
                "    A.ID ,A.BORROWER, " +
                "(select sum(B.EXPENSE_AMOUNT) EXPENSE_AMOUNT from FN_EXPENSE_DEBIT_ITEM b where A.ID = B.EXPENSE_DEBIT_ID) EXPENSE_AMOUNT," +
                "(SELECT SUM(B.REPAYMENT_MONEY) REPAYMENT_MONEY FROM FN_EXPENSE_REPAYMENT_ITEM \n" +
                "B WHERE A.ID = B.EXPENSE_DEBIT_ID) REPAYMENT_MONEY  \n" +
                " FROM FN_EXPENSE_DEBIT A \n"  ;

        if (Objects.nonNull(borrower)) {
            request.put("borrower", borrower);
            sql += "WHERE A.BORROWER =:borrower";
        }
        sql += " AND A.state = 'repayment' \n";
        return toJSONArray(find(sql, request));
    }

    public JSONArray findAllByCostCenter(String costCenterId){
        JSONObject request = new JSONObject();
        String sql ="" +
                " SELECT DISTINCT A.ID,\n" +
                "       A.BORROWER,\n" +
                "       A.REPAYMENT_DATE,\n" +
                "       A.EXPENSE_AMOUNT EXPENSE_AMOUNT,\n" +
                "       A.EXPENSE_REPAYMENT REPAYMENT_MONEY\n" +
                "FROM FN_EXPENSE_DEBIT A LEFT JOIN FN_EXPENSE_DEBIT_ITEM B ON A.ID = B.EXPENSE_DEBIT_ID\n" +
                "WHERE 1 = 1 \n"  ;

        if (Objects.nonNull(costCenterId)) {
            request.put("costCenterId", costCenterId);
            sql += " AND b.cost_center_id =:costCenterId";
        }
        sql += " AND A.REPAYMENT_DATE <= add_months(sysdate, -3)";
        sql += " AND A.state = 'repayment' \n";
        return toJSONArray(find(sql, request));
    }
    public JSONArray findInfoAllByEmployee(Map map){
        JSONObject request = new JSONObject();
        String sql ="" +
                "SELECT DISTINCT ACCOUNT_BANK_NAME,ACCOUNT_BANK,ACCOUNT_BANK_NUMBER,SUBBRANCH\n" +
                "FROM FN_REIMBURSEMENT_ITEM WHERE CLAIMANT_ID=:employee AND ACCOUNT_BANK_NAME LIKE '%'||:name||'%'\n"  ;
        request.put("employee", map.get("employee"));
        request.put("name", map.get("accountBankName"));
        return toJSONArray(find(sql, request));
    }
}
